# 虚拟基站数据表,包含以下四个，格式尽量与basic_db_ops相似
# 每个数据表都包含初始化、更新参数两个核心功能
# 8	 scene_bs_element	system_database	场景中基站的固定信息
# 9	 scene_user_element	system_database	场景中用户的固定信息
# 10 bs_status	system_database	场景中基站的状态
# 11 user_status	system_database	场景中用户的状态
# 为了保持一致(与虚拟场景表)，所有的输入engine均为Session

# basic_db_ops2v2为basic_db_ops2的更新后的版本，为适应新的数据库的变动：
# scene_user_element以及user_status两个表增添time_id, time_userrank，表示时间以及当前时间下用户的位置

from random import randint

from sqlalchemy import text, Float, ForeignKey, Boolean, Double
from sqlalchemy.orm import Session, relationship
from datetime import datetime


# 8	 scene_bs_element   场景中基站的固定信息
# 初始化基站固定信息
def initialize_bs_element(Session, bs_number, delete_option=True):
    """
    初始化基站数据
    :param Session: SQLAlchemy 的会话对象
    :param bs_number: 要初始化的基站数量
    :param delete_option: 是否删除现有数据，不删除的话预先手动删除
    :return: 成功舒适化就返回True
    """
    if delete_option:
        delete_all_scene_bs_element(Session)
    session = Session()
    inserted_count = 0  # 计数
    current_time = datetime.now()
    sql = text("""
        INSERT INTO public.scene_bs_element (
            time,
            bs_id_scene,
            type,
            is_physical_bs,
            lon,
            lat,
            bs_identifier,
            information
        ) VALUES (
            :time,
            :bs_id_scene,
            :type,
            :is_physical_bs,
            :lon,
            :lat,
            :bs_identifier,
            :information
        )
    """)

    try:
        for i in range(1, bs_number + 1):
            params = {
                "time": current_time,
                "bs_id_scene": str(i),
                # 第一个必然为宏基站
                "type": "宏基站" if i == 1 else "微基站",
                # 仿真必然不是实际基站
                "is_physical_bs": False if i != 1 else True,
                # 初始化可以不设置坐标
                "lon": 0,
                "lat": 0,
                # 基站必然归属自身
                "bs_identifier": i,
                "information": None
            }
            session.execute(sql, params)
            inserted_count += 1
        session.commit()
        # print(f"成功初始化第{bs_identifier}场景中{inserted_count}条基站固定信息数据")
        print(f"成功初始化{inserted_count}条基站固定信息数据")
        return True
    except Exception as e:
        # print(f"初始化第{bs_identifier}场景的基站固定信息数据时遇到错误: {e}")
        print(f"初始化基站固定信息数据时遇到错误: {e}")
        session.rollback()
        return False
    finally:
        session.close()

def get_scene_bs_element(Session, bs_id, paraname):
    """
    获取基站固定信息的参数值
    :param Session:
    :param bs_id: 读取基站的id
    :param paraname: 读取参数的名称
    :param bs_identifier: 归属场景id
    :return:直接返回获取参数的值
    """
    session = Session()
    try:
        sql = text(f"""
            SELECT {paraname} FROM public.scene_bs_element
            WHERE bs_id = :bs_id_value
        """)
        result = session.execute(sql, {
            "bs_id_value": bs_id,
        }).fetchone()[0]
        # print(f"成功读取第{bs_identifier}场景中第{bs_id}基站固定信息数据的{paraname}:{result}")
        print(f"成功读取第{bs_id}基站固定信息数据的{paraname}:{result}")
        return result

    except Exception as e:
        session.rollback()
        # print(f"读取第{bs_identifier}中第{bs_id}基站固定信息数据的{paraname}时遇到错误: {e}")
        print(f"读取第{bs_id}基站固定信息数据的{paraname}时遇到错误: {e}")
        return False
    finally:
        session.close()

# 删除基站信息
def delete_all_scene_bs_element(Session):
    """
    删除所有的scene_bs_element数据表记录
    :param Session: SQLAlchemy的会话工厂
    """
    session = Session()  # 创建会话实例
    try:
        # 编写删除所有数据的SQL
        sql = text("""
        DELETE FROM scene_bs_element
        """)
        session.execute(sql)

        # 重置序列的 SQL 语句，主键重新从1开始
        reset_sequence_sql = text("""
                    ALTER SEQUENCE public.scene_bs_element_bs_id_seq RESTART WITH 1
                """)
        session.execute(reset_sequence_sql)  # 执行重置序列操作
        # 提交事务
        session.commit()
        print("Deleted all records from scene_bs_element.")
    except Exception as e:
        # 如果出现异常，回滚事务
        session.rollback()
        print(f"An error occurred while deleting all records: {e}")
    finally:
        session.close()


def update_scene_bs_element(Session, updated_param, updated_data, bs_id):
    """
    主要是用来更新基站固定信息，主要为lon lat的参数，都为double形式
    :param Session:
    :param updated_param:更新的参数名，必然为字符串
    :param updated_data:更新的参数值
    :param bs_id: 基站的id，必然为整数
    :return:
    """
    session = Session()
    try:
        if not isinstance(updated_param, str):
            raise ValueError(f"Invalid column name: {updated_param}")

        update_sql = text(f"""
                        UPDATE public.scene_bs_element
                        SET {updated_param} = :updated_data
                        WHERE bs_id = :bs_id                    
                     """)

        params = {
            "updated_data": updated_data,
            "bs_id": bs_id,
        }
        session.execute(update_sql, params)
        print(f"成功更新第{bs_id}基站固定信息的{updated_param}为{updated_data}")
        session.commit()
    except Exception as e:
        # 回滚事务
        session.rollback()
        print(f"=更新第{bs_id} 的{updated_param}时遇到问题: {e}")
    finally:
        session.close()


# 读取场景中基站的固定信息
def read_scene_bs_element(Session, bs_id):
    sql = text("""
        SELECT * FROM scene_bs_element 
        WHERE bs_id = :bs_id_value
    """)

    with Session() as session:
        try:
            result = session.execute(sql, {"bs_id_value": bs_id}).fetchone()
            return result[0]
        except Exception as e:
            print(f"读取时遇到错误: {e}")
            return False
# scene_bs_element	场景中基站的固定信息  结束


# 9	scene_user_element 场景中用户的固定信息
# 初始化用户数量
def initialize_scene_user_element(Session, user_number, time_id=0, delete_option=True, show_info=False):
    """
    初始化场景用户元素数据并插入到数据库中。
    :param Session: SQLAlchemy 的会话对象
    :param user_number: 要初始化的用户元素数量
    :param delete_option: 是否删除现有数据，不启用的话预先手动删除,默认为True
    :param time_id: 当前初始化所处的仿真时间id
    :return: 成功初始化就返回True
    """
    if delete_option:
        delete_all_scene_user_element(Session)
    session = Session()
    inserted_count = 0
    current_time = datetime.now()
    sql = text("""
        INSERT INTO scene_user_element (
            time,
            user_id_scene,
            type,
            is_physical_user,
            lon,
            lat,
            bs_identifier,
            information,
            time_id,
            time_userrank
        ) VALUES (
            :time,
            :user_id_scene,
            :type,
            :is_physical_user,
            :lon,
            :lat,
            :bs_identifier,
            :information,
            :time_id,
            :time_userrank
        )
    """)

    try:
        # 类型随机生成
        type_list = ["Telephone", "Computer", "UAV"]
        for i in range(1, user_number + 1):
            random_type = randint(0, 2)
            params = {
                "time": current_time,

                # 实际上为某个场景内的用户序号，例如第一个场景里的第三个用户
                # 默认归为宏基站，所以按照顺序进行排列
                "user_id_scene": str(i),

                # 用户类型随机生成
                "type": type_list[random_type],

                # 先默认都是实际用户
                "is_physical_user": True,
                "lon": 0,
                "lat": 0,
                # 用户归属于一个基站， 最初归为宏基站
                "bs_identifier": 1,
                "information": None,
                "time_id": time_id,
                "time_userrank": i
            }
            session.execute(sql, params)
            inserted_count += 1
        session.commit()
        # print(f"成功初始化第{bs_identifier}场景中{inserted_count}条用户固定信息")
        if show_info:
            print(f"成功初始化{time_id}时刻的{inserted_count}条用户固定信息")
        return True
    except Exception as e:
        print(f"初始化场景用户固定信息时遇到错误: {e}")
        session.rollback()
        return False
    finally:
        session.close()


def delete_all_scene_user_element(Session):
    """
    删除所有的scene_user_element数据表记录
    :param Session: SQLAlchemy的会话工厂
    :return: None
    """

    session = Session()  # 创建会话实例
    try:
        # 编写删除所有数据的SQL
        sql = text("""
        DELETE FROM scene_user_element
        """)
        session.execute(sql)

        # 重置序列的 SQL 语句，主键重新从1开始
        reset_sequence_sql = text("""
                    ALTER SEQUENCE public.scene_user_element_user_id_seq RESTART WITH 1
                """)
        session.execute(reset_sequence_sql)  # 执行重置序列操作
        # 提交事务
        session.commit()
        print("Deleted all records from scene_user_element.")
    except Exception as e:
        # 如果出现异常，回滚事务
        session.rollback()
        print(f"An error occurred while deleting all records: {e}")
    finally:
        session.close()


def update_scene_user_element(Session, updated_param, updated_data, time_id, time_userrank):
    """
    主要是用来更新用户固定信息，主要为lon lat的参数，都为double形式
    :param Session:
    :param updated_param:更新的参数名，必然为字符串
    :param updated_data:更新的参数值
    :param time_id: 当前初始化所处的仿真时间id
    :param time_userrank: 当前初始化所处的仿真时间时的用户序号
    :return:
    """
    session = Session()
    try:
        if not isinstance(updated_param, str):
            raise ValueError(f"Invalid column name: {updated_param}")

        # 对于位置坐标的更新，一次更新一个
        update_sql = text(f"""
                               UPDATE public.scene_user_element
                               SET {updated_param} = :updated_data
                               WHERE time_id = :time_id
                               AND time_userrank = :time_userrank
                           """)
        params = {
            "updated_data": updated_data,
            "time_id" : time_id,
            "time_userrank": time_userrank
        }
        session.execute(update_sql, params)
        print(f"成功更新{time_id}时刻的第{time_userrank}用户的{updated_param}为{updated_data}")
        session.commit()
    except Exception as e:
        # 回滚事务
        session.rollback()
        print(f"=更新第{time_id}时刻的第{time_userrank}用户的{updated_param}时遇到问题: {e}")
    finally:
        session.close()


# 读取场景中用户的固定信息
def read_scene_user_element(Session, time_id, time_userrank):
    session = Session()
    try:
        sql = text("""
            SELECT * FROM public.scene_user_element
            WHERE time_id = :time_id
            AND time_userrank = :time_userrank
        """)
        result = session.execute(sql, {
            "time_id": time_id,
            "time_userrank" : time_userrank
        }).fetchone()
        print(f"成功读取第{time_id}时刻的第{time_userrank}用户的固定信息数据")
        return result

    except Exception as e:
        session.rollback()
        print(f"读取读取第{time_id}时刻的第{time_userrank}用户的固定信息数时遇到错误: {e}")
        return False
    finally:
        session.close()


# 获取某一个特定的参数的信息
def get_scene_user_element(Session, paraname, time_id, time_userrank):
    """
    获取用户固定信息的参数值，读取一个值
    :param Session:
    :param paraname: 读取参数的名称
    :param time_id:
    :param time_userrank:
    :return:直接返回所获取的参数
    """
    session = Session()
    try:
        sql = text(f"""
            SELECT {paraname} FROM public.scene_user_element
            WHERE time_id = :time_id
            AND time_userrank = :time_userrank
        """)
        result = session.execute(sql, {
            "time_id": time_id,
            "time_userrank": time_userrank
        }).fetchone()[0]
        print(f"成功读取第{time_id}时刻的第{time_userrank}用户固定信息数据的{paraname}:{result}")
        return result

    except Exception as e:
        session.rollback()
        print(f"读取第{time_id}时刻的第{time_userrank}用户固定信息数据的{paraname}时遇到错误:{e}")
        return False
    finally:
        session.close()

def count_scene_user_element(Session, time_id):
    """
    查询某一个时刻的用户数量
    :param Session:
    :param time_id: 查询的时刻
    :return: 返回用户的数量，如果没有的话则为0
    """
    session = Session()
    try:
        sql = text(f"""
                    SELECT COUNT(*)
                    FROM public.scene_user_element
                    WHERE time_id = :time_id
                """)

        params = {
            "time_id": time_id,
        }
        result = session.execute(sql, params).scalar()
        print(f"成功查询第{time_id}时刻用户的数量为:{result}")
        return result
    except Exception as e:
        session.rollback()
        print(f"查询第{time_id}时刻用户的数量时遇到错误: {e}")
    finally:
        session.close()
# scene_user_element 场景中用户的固定信息  结束

# 10 bs_status	system_database	场景中基站的状态
# 写入场景中基站的状态信息
def write_bs_status(engine, data):
    """
    使用数据库连接写入基站状态信息。

    :param engine: 数据库引擎
    :param data: 包含基站状态信息的字典
    :return: 写入成功返回True，否则返回False
    """
    sql = text("""
        INSERT INTO bs_status (
            time,
            bs_id,
            scene_id,
            information
        ) VALUES (
            :time,
            :bs_id,
            :scene_id,
            :information
        )
    """)

    with engine.connect() as connection:
        try:
            connection.execute(sql, {
                "time": data["time"],
                "bs_id": data["bs_id"],
                "scene_id": data["scene_id"],
                "information": data["information"]
            })
            connection.commit()  # 确保提交事务
            return True
        except Exception as e:
            print(f"写入时遇到错误: {e}")
            connection.rollback()  # 发生异常时回滚事务
            return False


def get_bs_status(Session, bs_id, paraname):
    """
    获取基站状态的参数值
    :param Session:
    :param bs_id: 获取基站状态的id
    :param parameter: 获取参数的名称
    :return:直接返货获取参数的值
    """
    #TODO 这里bs_id或者id应该都是可以的
    session = Session()
    try:
        sql = text(f"""
            SELECT {paraname} FROM public.scene_bs_element
            WHERE bs_id = :bs_id_value
        """)
        result = session.execute(sql, {
            "bs_id_value": bs_id
        }).fetchone()[0]
        print(f"成功读取第{bs_id}基站固定信息数据的{paraname}:{result}")
        return result

    except Exception as e:
        session.rollback()
        print(f"读取时遇到错误: {e}")
        return False
    finally:
        session.close()

# 读取场景中基站的状态信息
def read_bs_status(db, id):
    try:
        with Session(db) as session:
            sql = text("""
                SELECT * FROM bs_status 
                WHERE id = :id_value
            """)
            result = session.execute(sql, {
                "id_value": id
            }).fetchone()
            return result
    except Exception as e:
        print(f"读取时遇到错误: {e}")
        return False


# 更新场景中基站的状态信息
def update_bs_status(db, id, updated_data):
    try:
        with Session(db) as session:
            sql = text("""
                UPDATE bs_status
                SET time = :time,
                    bs_id = :bs_id,
                    scene_id = :scene_id,
                    information = :information
                WHERE id = :id
            """)
            session.execute(sql, {
                "id": id,
                "time": updated_data.time,
                "bs_id": updated_data.bs_id,
                "scene_id": updated_data.scene_id,
                "information": updated_data.information
            })
            session.commit()
    except Exception as e:
        print(f"更新时遇到错误: {e}")
        return False


# 删除场景中基站的状态信息
def delete_all_bs_status(db, id):
    try:
        with Session(db) as session:
            sql = text("""
                DELETE FROM bs_status 
                WHERE id = :id_value
            """)
            session.execute(sql, {
                "id_value": id
            })
            session.commit()
    except Exception as e:
        print(f"删除时遇到错误: {e}")
        return False


# 检查场景中基站的状态信息是否存在
def exist_bs_status(db, id):
    try:
        with Session(db) as session:
            sql = text("""
                SELECT 1 FROM bs_status 
                WHERE id = :id_value
            """)
            result = session.execute(sql, {
                "id_value": id
            }).fetchone()
            return result is not None
    except Exception as e:
        print(f"检查时遇到错误: {e}")
        return False


# bs_status	system_database	场景中基站的状态    结束


# 11 user_status	场景中用户的状态
# 写入场景中用户的状态信息
def write_user_status(engine, data):
    """
    使用数据库连接写入用户状态信息。

    :param engine: 数据库引擎
    :param data: 包含用户状态信息的字典
    :return: 写入成功返回True，否则返回False
    """
    sql = text("""
        INSERT INTO user_status (
            time,
            user_id,
            qos_request,
            qos,
            scene_id,
            information
        ) VALUES (
            :time,
            :user_id,
            :qos_request,
            :qos,
            :scene_id,
            :information
        )
    """)

    with engine.connect() as connection:
        try:
            connection.execute(sql, {
                "time": data["time"],
                "user_id": data["user_id"],
                "qos_request": data["qos_request"],
                "qos": data["qos"],
                "scene_id": data["scene_id"],
                "information": data["information"]
            })
            connection.commit()  # 确保提交事务
            return True
        except Exception as e:
            print(f"写入时遇到错误: {e}")
            connection.rollback()  # 发生异常时回滚事务
            return False


# 读取场景中用户的状态信息
def read_user_status(db, user_id):
    try:
        with Session(db) as session:
            sql = text("""
                SELECT * FROM user_status 
                WHERE user_id = :user_id_value
            """)
            result = session.execute(sql, {
                "user_id_value": user_id
            }).fetchone()
            return result
    except Exception as e:
        print(f"读取时遇到错误: {e}")
        return False


# 检查场景中用户的状态表user_status中是否存在在指定时间范围内的数据。
def exist_user_status(db, start_time=None, end_time=None):
    try:
        # 构建基本的SQL查询语句
        sql = text("""
            SELECT 1
            FROM user_status
            WHERE (:start_time IS NULL OR time >= :start_time)
              AND (:end_time IS NULL OR time <= :end_time)
        """)

        # 使用session执行查询
        with Session(db) as session:
            result = session.execute(sql, {
                "start_time": start_time,
                "end_time": end_time
            })

        # 判断是否有数据
        if result.fetchone():
            return True
        return False
    except Exception as e:
        print(f"输入的情况不支持查询: {e}")
        # 发生异常时返回False
        return False

# user_status	场景中用户的状态    结束

# 11.登录表单以及相关验证
def add_user(Session, username, password):
    """
    向用户表中添加新用户
    :param Session: 数据库会话工厂
    :param username: 用户名
    :param password: 用户密码
    :return: 操作成功返回 True，失败返回 False
    """
    session = Session()
    current_time = datetime.now()
    try:
        insert_sql = text("""
            INSERT INTO user_management (username, password, time)
            VALUES (:username, :password, :time)
        """)
        session.execute(insert_sql, {"username": username, "password": password, "time":current_time})
        session.commit()
        print("用户添加成功")
        return True
    except Exception as e:
        session.rollback()
        print(f"添加用户时出现错误: {e}")
        return False
    finally:
        session.close()

def get_all_users(Session):
    """
    从用户表中获取所有用户数据
    :param Session: 数据库会话工厂
    :return: 包含所有用户数据的列表，操作失败返回空列表
    """
    session = Session()
    try:
        select_sql = text("SELECT * FROM user_management")
        result = session.execute(select_sql)
        # 将结果转换为字典列表
        users = [dict(row) for row in result.mappings()]
        return users
    except Exception as e:
        print(f"获取用户数据时出现错误: {e}")
        return []
    finally:
        session.close()

def verify_user(Session, username, password):
    """
    验证传入的用户名和密码是否匹配数据库记录
    :param Session: 数据库会话工厂
    :param username: 待验证的用户名
    :param password: 待验证的密码
    :return: 验证成功返回 True，失败返回 False
    """
    session = Session()
    try:
        select_sql = text("SELECT * FROM user_management WHERE username = :username AND password = :password")
        result = session.execute(select_sql, {"username": username, "password": password})
        # 检查是否有匹配的记录
        user = result.fetchone()
        print(f"成功找到了用户")
        return bool(user)
    except Exception as e:
        print(f"验证用户时出现错误: {e}")
        return False
    finally:
        session.close()